package com.bilibili.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bilibili.dao.BaseDao;
import com.bilibili.dao.DataCenterDao;

public class DataCenterDaoImpl implements DataCenterDao {
	@Override
	public List<Map<String, Object>> queryPlayTrend(String timeRange, String category) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		List<Map<String, Object>> resultList = new ArrayList<>();
		sql = "SELECT date, play_count FROM statistics_play WHERE time_range = ? ORDER BY date ASC LIMIT 10";
		if (!"all".equals(category)) {
			sql = "SELECT date, play_count FROM statistics_play WHERE time_range = ? AND category = ? ORDER BY date ASC LIMIT 10";
		}
		try {
			con = BaseDao.getConn();
			ps = con.prepareStatement(sql);
			ps.setString(1, timeRange);
			if (!"all".equals(category)) {
				ps.setString(1, timeRange);
				ps.setString(2, category);
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
//	            row.put("id", rs.getInt("id"));
				row.put("date", rs.getDate("date"));
				row.put("playCount", rs.getInt("play_count"));
//	            row.put("timeRange", rs.getString("time_range"));
//	            row.put("category", rs.getString("category"));
//	            row.put("createTime", rs.getDate("create_time"));
				resultList.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return resultList;
	}

	@Override
	public List<Map<String, Object>> queryFanGrowthTrend(String timeRange, String category) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		List<Map<String, Object>> resultList = new ArrayList<>();
		sql = "SELECT date, new_fans FROM statistics_fan WHERE time_range = ? ORDER BY date ASC LIMIT 10";
		if (!"all".equalsIgnoreCase(category)) {
			sql = "SELECT date, new_fans FROM statistics_fan WHERE time_range = ? AND category = ? ORDER BY date ASC LIMIT 10";
		}
		try {
			con = BaseDao.getConn();
			ps = con.prepareStatement(sql);
			ps.setString(1, timeRange);
			if (!"all".equalsIgnoreCase(category)) {
				ps.setString(1, timeRange);
				ps.setString(2, category);
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
//	            row.put("id", rs.getInt("id"));
				row.put("date", rs.getDate("date"));
				row.put("newFans", rs.getInt("new_fans"));
//	            row.put("timeRange", rs.getString("time_range"));
//	            row.put("category", rs.getString("category"));
//	            row.put("createTime", rs.getTimestamp("create_time"));
				resultList.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return resultList;
	}

	@Override
	public List<Map<String, Object>> queryGenderDistribution(String timeRange, String category) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		List<Map<String, Object>> resultList = new ArrayList<>();
		sql = "SELECT gender,percentage " + "FROM statistics_gender " + "WHERE time_range = ? LIMIT 10";
		if (!"all".equalsIgnoreCase(category)) {
			sql = "SELECT gender,percentage " + "FROM statistics_gender " + "WHERE time_range = ? "
					+ "  AND category = ? LIMIT 10";
		}
		try {
			con = BaseDao.getConn();
			ps = con.prepareStatement(sql);
			ps.setString(1, timeRange);
			if (!"all".equalsIgnoreCase(category)) {
				ps.setString(1, timeRange);
				ps.setString(2, category);
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
//	            row.put("id", rs.getInt("id"));
				row.put("gender", rs.getString("gender"));
				row.put("percentage", rs.getDouble("percentage"));
//	            row.put("timeRange", rs.getString("time_range"));
//	            row.put("category", rs.getString("category"));
//	            row.put("createTime", rs.getTimestamp("create_time"));
				resultList.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return resultList;
	}

	@Override
	public List<Map<String, Object>> queryAgeDistribution(String timeRange, String category) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		List<Map<String, Object>> resultList = new ArrayList<>();
		sql = "SELECT age_group, percentage FROM statistics_age WHERE time_range = ? LIMIT 10";
		if (!"all".equalsIgnoreCase(category)) {
			sql = "SELECT age_group, percentage FROM statistics_age WHERE time_range = ? AND category = ? LIMIT 10";
		}
		try {
			con = BaseDao.getConn();
			ps = con.prepareStatement(sql);
			ps.setString(1, timeRange);
			if (!"all".equalsIgnoreCase(category)) {
				ps.setString(1, timeRange);
				ps.setString(2, category);
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
//	            row.put("id", rs.getInt("id"));
				row.put("ageGroup", rs.getString("age_group"));
				row.put("percentage", rs.getInt("percentage"));
//	            row.put("timeRange", rs.getString("time_range"));
//	            row.put("category", rs.getString("category"));
//	            row.put("createTime", rs.getTimestamp("create_time"));
				resultList.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return resultList;
	}

	@Override
	public List<Map<String, Object>> queryDetailedData(String timeRange, String category) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		List<Map<String, Object>> resultList = new ArrayList<>();
		sql = "SELECT " + "date, " + "play_count, " + "new_fans, " + "comment_count, " + "like_count, "
				 + "time_range, " + "category, " + "create_time " + "FROM statistics_detail "
				+ "WHERE time_range = ? " + "ORDER BY date DESC " + "LIMIT 10";
		if (!"all".equalsIgnoreCase(category)) {
			sql = "SELECT " + "date, " + "play_count, " + "new_fans, " + "comment_count, " + "like_count, "
					 + "time_range, " + "category, " + "create_time " + "FROM statistics_detail "
					+ "WHERE time_range = ? " + "AND category = ? " + "ORDER BY date DESC " + "LIMIT 10";
		}
		try {
			con = BaseDao.getConn();
			ps = con.prepareStatement(sql);
			ps.setString(1, timeRange);
			if (!"all".equalsIgnoreCase(category)) {
				ps.setString(1, timeRange);
				ps.setString(2, category);
			}
			rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
				row.put("date", rs.getDate("date"));
				row.put("playCount", rs.getInt("play_count"));
				row.put("newFans", rs.getInt("new_fans"));
				row.put("commentCount", rs.getInt("comment_count"));
				row.put("likeCount", rs.getInt("like_count"));
//	            row.put("timeRange", rs.getString("time_range"));
//	            row.put("category", rs.getString("category"));
//	            row.put("createTime", rs.getTimestamp("create_time"));
				resultList.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return resultList;
	}

	@Override
	public int getTotalPlayCount(String timeRange, String category) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total = 0;
		String sql = "SELECT SUM(play_count) as total FROM statistics_play WHERE time_range = ?";
		if (!"all".equals(category)) {
			sql += " AND category = ?";
		}

		try {
			con = BaseDao.getConn();
			ps = con.prepareStatement(sql);
			ps.setString(1, timeRange);
			if (!"all".equals(category)) {
				ps.setString(2, category);
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				total = rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return total;
	}

	@Override
	public int getFanCount(String timeRange, String category) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total = 0;
		String sql = "SELECT SUM(new_fans) as total FROM statistics_fan WHERE time_range = ?";
		if (!"all".equals(category)) {
			sql += " AND category = ?";
		}

		try {
			con = BaseDao.getConn();
			ps = con.prepareStatement(sql);
			ps.setString(1, timeRange);
			if (!"all".equals(category)) {
				ps.setString(2, category);
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				total = rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return total;
	}

	@Override
	public int getFanCount(String timeRange, String category, int userId) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total = 0;

		// 构建SQL查询语句：查询category_id=1的所有上传者的粉丝总数
		String sql = "SELECT " + "    COUNT(DISTINCT f.follower_id) AS total_fans " + "FROM " + "    follow f "
				+ "JOIN " + "    user u ON f.followee_id = u.user_id " + "JOIN "
				+ "    video v ON u.user_id = v.uploader_id " + "WHERE " + "    v.category_id = ?";

		try {
			con = BaseDao.getConn();
			ps = con.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			if (rs.next()) {
				total = rs.getInt("total_fans"); // 对应SQL中的别名total_fans
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return total;
	}

	@Override
	public List<Map<String, Object>> queryPlayTrend(String timeRange, String category, int userId) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> resultList = new ArrayList<>();

		try {
			con = BaseDao.getConn();
			con.setAutoCommit(false);

			if ("all".equals(category)) {
				// 清空统计表
				String truncateSql = "TRUNCATE TABLE statistics_play";
				try (PreparedStatement truncateStmt = con.prepareStatement(truncateSql)) {
					truncateStmt.executeUpdate();
				}

				// 动态生成日期范围
				String[] dateRange = getDateRange(timeRange);
				String startDate = dateRange[0];
				String endDate = dateRange[1];

				// 插入统计数据
				String insertSql = "INSERT INTO statistics_play (date, play_count, time_range, category, create_time, user_id) "
						+ "SELECT " + "    DATE(vp.play_time) AS date, " + "    SUM(v.view_count) AS play_count, "
						+ "    ? AS time_range, " + "    c.name AS category, " + "    NOW() AS create_time, "
						+ "    u.user_id " + "FROM video_play_counts vp " + "JOIN video v ON vp.video_id = v.video_id "
						+ "JOIN category c ON v.category_id = c.category_id " + "JOIN user u ON vp.user_id = u.user_id "
						+ "WHERE vp.play_time BETWEEN ? AND ? " + "  AND u.user_id = ? "
						+ "GROUP BY DATE(vp.play_time), c.name " + "ORDER BY DATE(vp.play_time) ASC";

				try (PreparedStatement pstmt = con.prepareStatement(insertSql)) {
					pstmt.setString(1, timeRange);
					pstmt.setString(2, startDate);
					pstmt.setString(3, endDate);
					pstmt.setInt(4, userId);
					int rowsInserted = pstmt.executeUpdate();
				}
			}

			String selectSql = "SELECT date, play_count, time_range, category, user_id " + "FROM statistics_play "
					+ "WHERE time_range = ? AND user_id = ? " + (category.equals("all") ? "" : "AND category = ? ")
					+ "ORDER BY date ASC";

			try (PreparedStatement pstmt = con.prepareStatement(selectSql)) {
				pstmt.setString(1, timeRange);
				pstmt.setInt(2, userId);

				if (!category.equals("all")) {
					pstmt.setString(3, category);
				}

				rs = pstmt.executeQuery();

				while (rs.next()) {
					Map<String, Object> row = new HashMap<>();
					row.put("date", rs.getDate("date"));
					row.put("playCount", rs.getInt("play_count"));
					row.put("timeRange", rs.getString("time_range"));
					row.put("category", rs.getString("category"));
					row.put("userId", rs.getInt("user_id"));
					resultList.add(row);
				}

			}

			con.commit();

		} catch (SQLException e) {
			try {
				if (con != null) {
					con.rollback();
					System.err.println("事务回滚成功");
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}

		return resultList;
	}

	private String[] getDateRange(String timeRange) {
		LocalDate now = LocalDate.now();
		LocalDate startDate;
		LocalDate endDate = now;

		switch (timeRange) {
		case "day":
			startDate = now.minusDays(6);
			break;
		case "month":
			startDate = now.withDayOfMonth(1);
			break;
		default:
			startDate = now.minusDays(6);
		}

		return new String[] { startDate.toString(), endDate.toString() };
	}

	private String getDateCondition(String timeRange) {
		Calendar calendar = Calendar.getInstance();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		calendar.setTime(new Date());
		String condition = "";

		switch (timeRange) {
		case "day":
			calendar.add(Calendar.DAY_OF_MONTH, -6);
			String weekStart = sdf.format(calendar.getTime());
			condition = "BETWEEN '" + weekStart + "' AND '" + sdf.format(new Date()) + "'";
			break;
		case "month":
			calendar.set(Calendar.DAY_OF_MONTH, 1);
			String monthStart = sdf.format(calendar.getTime());
			calendar.add(Calendar.MONTH, 1);
			calendar.add(Calendar.DAY_OF_MONTH, -1);
			String monthEnd = sdf.format(calendar.getTime());
			condition = "BETWEEN '" + monthStart + "' AND '" + monthEnd + "'";
			break;
		default:
			calendar.add(Calendar.DAY_OF_MONTH, -7);
			String weekStart1 = sdf.format(calendar.getTime());
			condition = "BETWEEN '" + weekStart1 + "' AND '" + sdf.format(new Date()) + "'";
		}

		return condition;
	}

	@Override
	public List<Map<String, Object>> queryFanGrowthTrend(String timeRange, String category, int userId) {
		Connection con = null;
		PreparedStatement truncatePs = null;
		PreparedStatement insertPs = null;
		PreparedStatement queryPs = null;
		ResultSet rs = null;
		List<Map<String, Object>> resultList = new ArrayList<>();

		try {
			con = BaseDao.getConn();
			con.setAutoCommit(false); // 开启事务

			// 生成日期范围
			String dateCondition = getDateCondition(timeRange);
			String[] dateRange = dateCondition.replace("BETWEEN '", "").replace("' AND '", ",").replace("'", "")
					.split(",");
			String startDate = dateRange[0];
			String endDate = dateRange[1];

			// 清空统计表数据
			String truncateSql = "TRUNCATE TABLE statistics_fan";
			truncatePs = con.prepareStatement(truncateSql);
			truncatePs.executeUpdate();
			truncatePs.close();

			String insertSql = "INSERT INTO statistics_fan (date, new_fans, time_range, category, create_time) "
					+ "SELECT f.create_time AS date, " + "       COUNT(f.followee_id) AS new_fans, "
					+ "       ? AS time_range, " + "       c.name AS category, " + "       NOW() AS create_time "
					+ "FROM follow f " + "JOIN video v ON f.followee_id = v.uploader_id "
					+ "JOIN category c ON v.category_id = c.category_id " + "JOIN user u ON v.uploader_id = u.user_id "
					+ "WHERE f.create_time BETWEEN ? AND ? " + "AND u.user_id = ? "
					+ "GROUP BY f.create_time, v.category_id, c.name " + "ORDER BY f.create_time ASC";

			insertPs = con.prepareStatement(insertSql);
			insertPs.setString(1, timeRange);
			insertPs.setString(2, startDate);
			insertPs.setString(3, endDate);
			insertPs.setInt(4, userId); // 假设用户ID为1，可根据实际需求修改
			insertPs.executeUpdate();
			insertPs.close();

			// 构建查询SQL
			String querySql;
			if ("all".equalsIgnoreCase(category)) {
				querySql = "SELECT date, new_fans " + "FROM statistics_fan " + "WHERE time_range = ? "
						+ "ORDER BY date ASC LIMIT 10";
				queryPs = con.prepareStatement(querySql);
				queryPs.setString(1, timeRange);
			} else {
				querySql = "SELECT date, new_fans " + "FROM statistics_fan " + "WHERE time_range = ? AND category = ? "
						+ "ORDER BY date ASC LIMIT 10";
				queryPs = con.prepareStatement(querySql);
				queryPs.setString(1, timeRange);
				queryPs.setString(2, category);
			}

			// 执行查询
			rs = queryPs.executeQuery();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
				row.put("date", rs.getDate("date"));
				row.put("newFans", rs.getInt("new_fans"));
				resultList.add(row);
			}

			con.commit(); // 提交事务

		} catch (SQLException e) {
			try {
				if (con != null)
					con.rollback();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			// 关闭所有资源
			BaseDao.closeAll(con, truncatePs);
		}

		return resultList;
	}

	@Override
	public List<Map<String, Object>> queryGenderDistribution(String timeRange, String category, int userId) {
		Connection con = null;
		PreparedStatement truncatePs = null;
		PreparedStatement insertPs = null;
		PreparedStatement queryPs = null;
		ResultSet rs = null;
		List<Map<String, Object>> resultList = new ArrayList<>();

		try {
			con = BaseDao.getConn();
			con.setAutoCommit(false); // 开启事务

			// 1. 生成日期范围条件
			String dateCondition = getDateCondition(timeRange);
			String[] dateRange = dateCondition.replace("BETWEEN '", "").replace("' AND '", ",").replace("'", "")
					.split(",");
			String startDate = dateRange[0];
			String endDate = dateRange[1];

			// 2. 清空统计表
			String truncateSql = "TRUNCATE TABLE statistics_gender";
			truncatePs = con.prepareStatement(truncateSql);
			truncatePs.executeUpdate();
			truncatePs.close();

			// 3. 插入统计数据（修正SQL语法和参数绑定）
			String insertSql = "INSERT INTO statistics_gender (gender, percentage, time_range, category, create_time) "
					+ "SELECT " + "    CASE u.gender " + "        WHEN 0 THEN '未知' " + "        WHEN 1 THEN '男' "
					+ "        WHEN 2 THEN '女' " + "    END AS gender, " + "    ROUND(COUNT(vpc.record_id) * 100.0 / "
					+ "          IFNULL((SELECT COUNT(*) FROM video_play_counts vpc2 "
					+ "                  JOIN video v2 ON vpc2.video_id = v2.video_id "
					+ "                  WHERE v2.category_id = v.category_id "
					+ "                    AND vpc2.play_time " + dateCondition + "), 1), 2) AS percentage, "
					+ "    ? AS time_range, " + "    c.name AS category, " + "    NOW() AS create_time " + "FROM "
					+ "    video_play_counts vpc " + "JOIN " + "    video v ON vpc.video_id = v.video_id " + "JOIN "
					+ "    category c ON v.category_id = c.category_id " + "JOIN "
					+ "    user u ON vpc.user_id = u.user_id " + "WHERE " + "    vpc.play_time " + dateCondition
					+ " AND u.user_id = ? " + "GROUP BY " + "    v.category_id, u.gender";

			insertPs = con.prepareStatement(insertSql);
			insertPs.setString(1, timeRange);
			insertPs.setInt(2, userId);
			insertPs.executeUpdate();
			insertPs.close();

			// 4. 构建查询SQL
			String querySql;
			if ("all".equalsIgnoreCase(category)) {
				querySql = "SELECT gender, percentage FROM statistics_gender " + "WHERE time_range = ? LIMIT 10";
				queryPs = con.prepareStatement(querySql);
				queryPs.setString(1, timeRange);
			} else {
				querySql = "SELECT gender, percentage FROM statistics_gender "
						+ "WHERE time_range = ? AND category = ? LIMIT 10";
				queryPs = con.prepareStatement(querySql);
				queryPs.setString(1, timeRange);
				queryPs.setString(2, category);
			}

			// 5. 执行查询
			rs = queryPs.executeQuery();
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
				row.put("gender", rs.getString("gender"));
				row.put("percentage", rs.getDouble("percentage"));
				resultList.add(row);
			}

			con.commit(); // 提交事务

		} catch (SQLException e) {
			try {
				if (con != null)
					con.rollback(); // 事务回滚
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			// 关闭所有资源
			BaseDao.closeAll(con, truncatePs);
		}

		return resultList;
	}

	@Override
	public List<Map<String, Object>> queryAgeDistribution(String timeRange, String category, int userId) {
		Connection con = null;
		PreparedStatement truncatePs = null;
		PreparedStatement insertPs = null;
		PreparedStatement selectPs = null;
		ResultSet rs = null;
		List<Map<String, Object>> resultList = new ArrayList<>();

		try {
			con = BaseDao.getConn();
			con.setAutoCommit(false); // 开启事务

			// 1. 生成日期范围（使用getDateCondition生成条件字符串）
			String dateCondition = getDateCondition(timeRange);
			// 从条件字符串中提取开始和结束日期
			String[] dateRange = dateCondition.replace("BETWEEN '", "").replace("' AND '", ",").replace("'", "")
					.split(",");
			String startDate = dateRange[0];
			String endDate = dateRange[1];

			// 2. 清空统计表
			String truncateSql = "TRUNCATE TABLE statistics_age";
			truncatePs = con.prepareStatement(truncateSql);
			truncatePs.executeUpdate();
			truncatePs.close();

			// 3. 插入统计数据
			// 3. 插入统计数据（添加user_id条件）
			String insertSql = "INSERT INTO statistics_age (age_group, percentage, time_range, category, user_id, create_time) "
					+ "SELECT " + "    CASE "
					+ "        WHEN TIMESTAMPDIFF(YEAR, u.birthday, CURDATE()) < 18 THEN '18岁以下' "
					+ "        WHEN TIMESTAMPDIFF(YEAR, u.birthday, CURDATE()) BETWEEN 18 AND 24 THEN '18-24岁' "
					+ "        WHEN TIMESTAMPDIFF(YEAR, u.birthday, CURDATE()) BETWEEN 25 AND 30 THEN '25-30岁' "
					+ "        WHEN TIMESTAMPDIFF(YEAR, u.birthday, CURDATE()) BETWEEN 31 AND 40 THEN '31-40岁' "
					+ "        WHEN TIMESTAMPDIFF(YEAR, u.birthday, CURDATE()) BETWEEN 41 AND 50 THEN '41-50岁' "
					+ "        ELSE '50岁以上' " + "    END AS age_group, " + "    ROUND(COUNT(vpc.record_id) * 100.0 / "
					+ "          (SELECT COUNT(*) FROM video_play_counts vpc2 "
					+ "           JOIN video v2 ON vpc2.video_id = v2.video_id "
					+ "           WHERE v2.category_id = v.category_id " + "             AND vpc2.play_time "
					+ dateCondition + " " + "             AND vpc2.user_id = ?), 2) AS percentage, " + // 子查询添加user_id条件
					"    ? AS time_range, " + "    c.name AS category, " + "    ?, " + // 插入user_id
					"    NOW() AS create_time " + "FROM " + "    video_play_counts vpc " + "JOIN "
					+ "    video v ON vpc.video_id = v.video_id " + "JOIN "
					+ "    category c ON v.category_id = c.category_id " + "JOIN "
					+ "    user u ON vpc.user_id = u.user_id " + "WHERE " + "    vpc.play_time " + dateCondition + " "
					+ "    AND u.birthday IS NOT NULL " + "    AND vpc.user_id = ? " + "GROUP BY "
					+ "    v.category_id, age_group";

			insertPs = con.prepareStatement(insertSql);
			insertPs.setInt(1, userId);
			insertPs.setString(2, timeRange);
			insertPs.setInt(3, userId);
			insertPs.setInt(4, userId);
			insertPs.executeUpdate();
			insertPs.close();

			// 4. 查询统计结果
			String selectSql;
			if ("all".equalsIgnoreCase(category)) {
				selectSql = "SELECT age_group, percentage, category, user_id FROM statistics_age "
						+ "WHERE time_range = ? AND user_id = ? " + "ORDER BY category, " + "CASE age_group "
						+ "    WHEN '18岁以下' THEN 1 " + "    WHEN '18-24岁' THEN 2 " + "    WHEN '25-30岁' THEN 3 "
						+ "    WHEN '31-40岁' THEN 4 " + "    WHEN '41-50岁' THEN 5 " + "    ELSE 6 " + "END";
				selectPs = con.prepareStatement(selectSql);
				selectPs.setString(1, timeRange);
				selectPs.setInt(2, 1);
			} else {
				selectSql = "SELECT age_group, percentage, user_id FROM statistics_age "
						+ "WHERE time_range = ? AND category = ? AND user_id = ? " + "ORDER BY " + "CASE age_group "
						+ "    WHEN '18岁以下' THEN 1 " + "    WHEN '18-24岁' THEN 2 " + "    WHEN '25-30岁' THEN 3 "
						+ "    WHEN '31-40岁' THEN 4 " + "    WHEN '41-50岁' THEN 5 " + "    ELSE 6 " + "END";
				selectPs = con.prepareStatement(selectSql);
				selectPs.setString(1, timeRange);
				selectPs.setString(2, category);
				selectPs.setInt(3, userId);
			}

			rs = selectPs.executeQuery();

			// 5. 处理结果集
			while (rs.next()) {
				Map<String, Object> row = new HashMap<>();
				row.put("ageGroup", rs.getString("age_group"));
				row.put("percentage", rs.getDouble("percentage"));
				if ("all".equalsIgnoreCase(category)) {
					row.put("category", rs.getString("category"));
				}
				resultList.add(row);
			}

			con.commit(); // 提交事务

		} catch (SQLException e) {
			try {
				if (con != null)
					con.rollback(); // 事务回滚
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			// 确保所有资源都被关闭
			BaseDao.closeAll(con, truncatePs);
		}

		return resultList;
	}
}
